package co.fitstart.scheduler.service.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.demo2do.core.utils.DateFormatUtils;
import com.demo2do.core.utils.DateUtils;

import co.fitstart.scheduler.Constant;
import co.fitstart.scheduler.entity.CourseRoundExpireNotice;
import co.fitstart.scheduler.entity.CourseRoundUnfreezeNotice;
import co.fitstart.scheduler.entity.ScheduleNotice;
import co.fitstart.scheduler.handler.SmsSender;
import co.fitstart.scheduler.persistence.GenericJdbcSupport;
import co.fitstart.scheduler.service.SmsNoticeService;

/**
 * 
 * @author haiyan_xiao
 *
 */
@Service("smsNoticeService")
public class SmsNoticeServiceImpl implements SmsNoticeService {

    @Autowired
    private GenericJdbcSupport genericJdbcSupport;
    
    @Autowired
    private SmsSender smsSender;

    /*
     * 
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#toExpiryCourseRound(java.util.Date)
     */
    public int toExpiryCourseRound(Date date) {
        Map<String, Date> times = this.getTimes(date, 14);
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        parameters.put("ready", Constant.ROUND_STATUS_READY);
        parameters.put("activate", Constant.ROUND_STATUS_ACTIVATE);
        parameters.put("begin", times.get("begin"));
        parameters.put("end", times.get("end"));
        
        return genericJdbcSupport.queryForInt("SELECT COUNT(*) FROM course_round cr"
                + " JOIN course c ON cr.course_id=c.id"
                + " WHERE cr.is_disabled = false AND cr.course_type IN( :individual, :year) AND c.is_experience = false"
                + " AND cr.course_status IN ( :ready, :activate) AND cr.effective_end >= :begin"
                + " AND cr.effective_end <= :end", parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#noticeToExpireRound(java.util.Date)
     */
    public void noticeToExpireRound(Date date) {
        Map<String, Date> times = this.getTimes(date, 14);

        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseAmount", Constant.COURSE_AMOUNT);
        parameters.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        parameters.put("ready", Constant.ROUND_STATUS_READY);
        parameters.put("activate", Constant.ROUND_STATUS_ACTIVATE);
        parameters.put("begin", times.get("begin"));
        parameters.put("end", times.get("end"));

        List<CourseRoundExpireNotice> notices = genericJdbcSupport.queryForList(
                "SELECT stu.mobile,s.name site,cr.course_type,cra.value course_amount,c.hours,cr.effective_end effective_end"
                + " FROM course_round cr"
                + " JOIN course c ON cr.course_id=c.id"
                + " JOIN student stu ON cr.student_id=stu.id"
                + " JOIN site s ON cr.site_id = s.id"
                + " LEFT JOIN course_round_attr cra ON cr.id=cra.course_round_id AND cra.name = :courseAmount"
                + " WHERE cr.is_disabled = false AND cr.course_type IN( :individual, :year) AND c.is_experience = false"
                + " AND cr.course_status IN ( :ready, :activate) AND cr.effective_end >= :begin"
                + " AND cr.effective_end <= :end", parameters, CourseRoundExpireNotice.class);
        
        for(CourseRoundExpireNotice notice : notices) {
            smsSender.sendTwoWeeksLaterExpireNotice(notice.getMobile(), notice.getSite(), notice.getCourseType(),
                        notice.getCourseAmount() == null ? (notice.getHours() + "") : notice.getCourseAmount(), notice.getEffectiveEnd());
        }
    }


    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#toNoticeSchedule(java.util.Date)
     */
    public int toNoticeSchedule(Date date) {
        Date sendTime = DateUtils.parseDate(DateFormatUtils.format(date, "yyyy-MM-dd HH:mm") + ":00", "yyyy-MM-dd HH:mm:ss");
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("sendTime", sendTime);
        parameters.put("guBei", Constant.GU_BEI_NAME);
        parameters.put("wuJiaoChang", Constant.WU_JIAO_CHANG_NAME);
        parameters.put("fuXing", Constant.FU_XING_NAME);
        
        return genericJdbcSupport.queryForInt("SELECT COUNT(*) FROM schedule_sms_notice"
                + " WHERE is_disabled = false AND send_time = :sendTime"
                + " AND site NOT IN (:guBei, :wuJiaoChang, :fuXing)", parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#noticeSchedule(java.util.Date)
     */
    public void noticeSchedule(Date date) {
        Date sendTime = DateUtils.parseDate(DateFormatUtils.format(date, "yyyy-MM-dd HH:mm") + ":00", "yyyy-MM-dd HH:mm:ss");
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("sendTime", sendTime);
        parameters.put("guBei", Constant.GU_BEI_NAME);
        parameters.put("wuJiaoChang", Constant.WU_JIAO_CHANG_NAME);
        parameters.put("fuXing", Constant.FU_XING_NAME);
        
        List<ScheduleNotice> notices = genericJdbcSupport.queryForList(
                "SELECT openid, mobile,site,coach,occur_date occurDate,occur_hour occurHour,course_type courseType, course_name courseName"
                + " FROM schedule_sms_notice WHERE is_disabled = false"
                + " AND send_time = :sendTime AND site NOT IN (:guBei, :wuJiaoChang, :fuXing)",
                parameters, ScheduleNotice.class);
        
        if(notices != null && !notices.isEmpty()) {
            for(ScheduleNotice notice : notices) {
                if(!"18516117051".equals(notice.getMobile().trim()) && !"17602184361".equals(notice.getMobile().trim())) {
                    smsSender.sendTwoHoursLaterScheduleNotice(notice.getOpenid(), notice.getCourseName(), notice.getMobile(), notice.getSite(),
                            notice.getCoach(), notice.getOccurDate(), notice.getOccurHour());
                }
            }
        }
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#toUnfreezeCourseRound(java.util.Date)
     */
    public int toUnfreezeCourseRound(Date date) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseAmount", Constant.COURSE_AMOUNT);
        parameters.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        parameters.put("hold", Constant.ROUND_STATUS_HOLD);
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(DateUtils.addDays(date, 9), "yyyy-MM-dd") + " 00:30:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(DateUtils.addDays(date, 10), "yyyy-MM-dd") + " 00:30:00", "yyyy-MM-dd HH:mm:ss"));
        
        return genericJdbcSupport.queryForInt(
                "SELECT COUNT(*) FROM course_round cr"
                + " JOIN course c ON cr.course_id=c.id"
                + " WHERE cr.is_disabled = false AND cr.course_type IN ( :individual, :year)"
                + " AND c.is_experience=false AND cr.course_status=:hold"
                + " AND cr.hold_end_date>:begin AND cr.hold_end_date<=:end", parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#noticeToUnfreezeRound(java.util.Date)
     */
    public void noticeToUnfreezeRound(Date date) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseAmount", Constant.COURSE_AMOUNT);
        parameters.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
        parameters.put("year", Constant.COURSE_TYPE_YEAR);
        parameters.put("hold", Constant.ROUND_STATUS_HOLD);
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(DateUtils.addDays(date, 9), "yyyy-MM-dd") + " 00:30:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(DateUtils.addDays(date, 10), "yyyy-MM-dd") + " 00:30:00", "yyyy-MM-dd HH:mm:ss"));
        
        List<CourseRoundUnfreezeNotice> notices = genericJdbcSupport.queryForList(
                "SELECT stu.mobile,s.name site,cr.course_type courseType,c.hours,cra.value courseAmount,cr.finished"
                + " FROM course_round cr"
                + " JOIN student stu ON cr.student_id=stu.id"
                + " JOIN site s ON cr.site_id=s.id"
                + " JOIN course c ON cr.course_id=c.id"
                + " LEFT JOIN course_round_attr cra ON cr.id=cra.course_round_id AND cra.name=:courseAmount"
                + " WHERE cr.is_disabled = false AND cr.course_type IN ( :individual, :year)"
                + " AND c.is_experience=false AND cr.course_status=:hold"
                + " AND cr.hold_end_date>:begin AND cr.hold_end_date<=:end", parameters, CourseRoundUnfreezeNotice.class);
        
        if(notices != null && !notices.isEmpty()) {
            for(CourseRoundUnfreezeNotice notice : notices) {
                smsSender.sendTenDaysLaterUnfreezeNotice(notice.getMobile(), notice.getSite(), notice.getCourseType(),
                            notice.getCourseType() == 1 ? ((notice.getCourseAmount() == null ? notice.getHours() : Integer.parseInt(notice.getCourseAmount())) - notice.getFinished()) : null,
                                    DateUtils.addDays(date, 10));
            }
        }
    }

    /**
     * 
     * @param date
     * @return
     */
    private Map<String, Date> getTimes(Date date, int day) {
        Map<String, Date> times = new HashMap<>();
        
        String twoWeeksLater = DateFormatUtils.format(DateUtils.addDays(date, day), "yyyy-MM-dd");
        
        times.put("begin", DateUtils.parseDate(twoWeeksLater + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        times.put("end", DateUtils.parseDate(twoWeeksLater + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return times;
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#toNoticeScheduleToday(java.util.Date)
     */
    public int toNoticeScheduleToday(Date date) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("ready", Constant.SCHEDULE_STATUS_READY);
        parameters.put("today", date);
        parameters.put("guBei", Constant.GU_BEI_ID);
        parameters.put("wuJiaoChang", Constant.WU_JIAO_CHANG_ID);
        
        return genericJdbcSupport.queryForInt(
                "SELECT COUNT(*) FROM course_schedule cs JOIN course c ON cs.course_id=c.id"
                + " WHERE cs.is_disabled=false AND c.is_experience=false"
                + " AND cs.schedule_status=:ready AND cs.occur_date=:today"
                + " AND cs.site_id IN (:guBei, :wuJiaoChang)", parameters);
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#toNoticeScheduleTodayAtNine(java.util.Date)
     */
    public int toNoticeScheduleTodayAtNine(Date date) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("ready", Constant.SCHEDULE_STATUS_READY);
        parameters.put("today", date);
        parameters.put("fuXing", Constant.FU_XING_ID);
        
        return genericJdbcSupport.queryForInt(
                "SELECT COUNT(*) FROM course_schedule cs JOIN course c ON cs.course_id=c.id"
                + " WHERE cs.is_disabled=false AND c.is_experience=false"
                + " AND cs.schedule_status=:ready AND cs.occur_date=:today"
                + " AND cs.site_id = :fuXing", parameters);
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#noticeScheduleToday(java.util.Date)
     */
    public void noticeScheduleToday(Date date) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
        parameters.put("ready", Constant.SCHEDULE_STATUS_READY);
        parameters.put("today", date);
        parameters.put("guBei", Constant.GU_BEI_ID);
        parameters.put("wuJiaoChang", Constant.WU_JIAO_CHANG_ID);
        
        List<ScheduleNotice> notices = genericJdbcSupport.queryForList(
                "SELECT stu.mobile,s.name site,p.name coach,"
                + "DATE_FORMAT(cs.occur_date,'%m/%d') occurDate,cs.occur_hour occurHour,"
                + "CASE cs.course_type WHEN :individual THEN '1V1' ELSE '年卡' END courseType"
                + " FROM course_schedule cs"
                + " JOIN course c ON cs.course_id=c.id"
                + " JOIN student stu ON cs.student_id=stu.id"
                + " JOIN site s ON cs.site_id=s.id"
                + " JOIN principal p ON cs.coach_id=p.id"
                + " WHERE cs.is_disabled=false AND c.is_experience=false"
                + " AND cs.schedule_status=:ready AND cs.occur_date=:today"
                + " AND cs.site_id IN (:guBei, :wuJiaoChang);",
                parameters, ScheduleNotice.class);
        
        // FIXME
        if(notices != null && !notices.isEmpty()) {
            for(ScheduleNotice notice : notices) {
                if(!"18516117051".equals(notice.getMobile().trim()) && !"17602184361".equals(notice.getMobile().trim())) {
                    smsSender.sendTwoHoursLaterScheduleNotice(null, null, notice.getMobile(), notice.getSite(),
                            notice.getCoach(), notice.getOccurDate(), notice.getOccurHour());
                }
            }
        }
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.SmsNoticeService#noticeScheduleTodayAtNine(java.util.Date)
     */
    public void noticeScheduleTodayAtNine(Date date) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("individual", Constant.COURSE_TYPE_INDIVIDUAL);
        parameters.put("ready", Constant.SCHEDULE_STATUS_READY);
        parameters.put("today", date);
        parameters.put("fuXing", Constant.FU_XING_ID);
        
        List<ScheduleNotice> notices = genericJdbcSupport.queryForList(
                "SELECT stu.mobile,s.name site,p.name coach,"
                + "DATE_FORMAT(cs.occur_date,'%m/%d') occurDate,cs.occur_hour occurHour,"
                + "CASE cs.course_type WHEN :individual THEN '1V1' ELSE '年卡' END courseType"
                + " FROM course_schedule cs"
                + " JOIN course c ON cs.course_id=c.id"
                + " JOIN student stu ON cs.student_id=stu.id"
                + " JOIN site s ON cs.site_id=s.id"
                + " JOIN principal p ON cs.coach_id=p.id"
                + " WHERE cs.is_disabled=false AND c.is_experience=false"
                + " AND cs.schedule_status=:ready AND cs.occur_date=:today"
                + " AND cs.site_id = :fuXing",
                parameters, ScheduleNotice.class);
        
        // FIXME
        if(notices != null && !notices.isEmpty()) {
            for(ScheduleNotice notice : notices) {
                if(!"18516117051".equals(notice.getMobile().trim()) && !"17602184361".equals(notice.getMobile().trim())) {
                    smsSender.sendTwoHoursLaterScheduleNotice(null, null, notice.getMobile(), notice.getSite(),
                            notice.getCoach(), notice.getOccurDate(), notice.getOccurHour());
                }
            }
        }
    }
}
